package com.example.excel.service;

import com.example.excel.domain.Student;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author HTT
 */
@Service
public class StudentService {

    public List<Student> initData(){
        List<Student> list = new ArrayList<>();
        Student student = new Student();
        student.setId(1);
        student.setName("张三");
        student.setSex("男");
        student.setAddress("江苏");
        student.setPhone("19991198111");
        list.add(student);
        student = new Student();
        student.setId(2);
        student.setName("李四");
        student.setSex("女");
        student.setAddress("南京");
        student.setPhone("1887119121");
        list.add(student);
        return list;
    }

    public void export(HttpServletResponse response) throws IOException {

        //获取学生数据
        List<Student> students = initData();
        //获取标题
        String[] titles = {"编号","姓名","性别","地址","电话"};
        //.xls格式
        HSSFWorkbook workbook = new HSSFWorkbook();

        //.xlsx格式
        //XSSFWorkbook workbook = new XSSFWorkbook();


        //新增工作表
        HSSFSheet sheet = workbook.createSheet("学生表");

        //单元格样式对象
        HSSFCellStyle cellStyle = this.getColumnStyle(workbook, true, true, HorizontalAlignment.CENTER,VerticalAlignment.CENTER);

        //创建第一行
        HSSFRow header = sheet.createRow(0);
        HSSFCell headerCell = header.createCell(0);
        headerCell.setCellValue("学生信息表");
        CellRangeAddress region = new CellRangeAddress(0, 0 , 0, titles.length-1);
        sheet.addMergedRegion(region);

        /**
         * - 左边距
         * - 上边距
         * - 右边距
         * - 下边距
         * - 开始列
         * - 开始行
         * - 结束列
         * - 结束行
         * 在工作表`drawing`上,
         * 创建了一个单元格批注 comment。
         * 这个批注的锚点设置在从单元格 headerCell 开始,延伸 5 列 6 行。
         */
        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(new HSSFClientAnchor(0, 0, 0, 0,
                (short) headerCell.getColumnIndex(),
                headerCell.getRowIndex(),
                (short) (headerCell.getColumnIndex() + 5),
                headerCell.getRowIndex() + 6));
        comment.setString(new HSSFRichTextString("这是标题信息哦！"));

        headerCell.setCellComment(comment);

        //创建第一行
        HSSFRow titleRow = sheet.createRow(1);

        //写入标题
        for(int i = 0 ; i < titles.length ; i++ ){
            HSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyle);
        }

        for(int i = 0 ; i < students.size() ; i++ ){

            Student student = students.get(i);

            HSSFRow row = sheet.createRow(i+2);

            for (int j = 0; j < titles.length; j++) {
                HSSFCell cell = null;
                switch (j) {
                    case 0:
                        cell = row.createCell(j);
                        cell.setCellValue(i);
                        break;
                    case 1:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getName());
                        break;
                    case 2:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getSex());
                        break;
                    case 3:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getAddress());
                        break;
                    case 4:
                        cell = row.createCell(j);
                        cell.setCellValue(student.getPhone());
                        break;
                }
            }
        }


        String fileName = "学生表统计";
        String codeFileName = new String((fileName + ".xls").getBytes("UTF-8"), "ISO-8859-1");
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + codeFileName);
        OutputStream out = response.getOutputStream();
        workbook.write(response.getOutputStream());
        out.flush();
        out.close();
        workbook.close();

    }

    /**
     * 单元格样式
     * @param workbook
     * @param fontBold 是否加粗
     * @param isBorder 是否带边框
     * @param horizontalAlignment 水平对齐格式
     * @param verticalAlignment 垂直对齐格式
     * @return
     */
    private HSSFCellStyle getColumnStyle(HSSFWorkbook workbook,
                                         boolean fontBold,
                                         boolean isBorder,
                                         HorizontalAlignment horizontalAlignment,
                                         VerticalAlignment verticalAlignment) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //设置字体名字
        font.setFontName("宋体");
        font.setBold(fontBold);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        // 是设置前景色不是背景色
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        if (isBorder) {
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(IndexedColors.BLACK.index);
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(IndexedColors.BLACK.index);
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(IndexedColors.BLACK.index);
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(IndexedColors.BLACK.index);
        }
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(horizontalAlignment);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(verticalAlignment);
        return style;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型，需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == CellType.NUMERIC){
            cell.setCellType(CellType.STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            //数字
            case NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            //字符串
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            //Boolean
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //公式
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            //空值
            case BLANK:
                cellValue = "";
                break;
            //故障
            case ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


    public void analysis() throws IOException {
        // 通过输入流，读取excel文件
        FileInputStream in = new FileInputStream("F:\\学生表统计.xls");

        // excel文件对象
        Workbook workbook = new HSSFWorkbook(in);

        // 通过工作簿的名字获取工作簿
        Sheet sheet = workbook.getSheet("学生表");

        // 通过工作簿的下标获取工作簿
        //Sheet sheet = workbook.getSheetAt(0);

        // 获取首行下标
        int firstRowNum = sheet.getFirstRowNum();

        // 获取尾行下标
        int lastRowNum = sheet.getLastRowNum();

        for(int i = firstRowNum ; i <=lastRowNum ; i++){

            Row row = sheet.getRow(i);

            Cell cell = null;
            if(row != null){
                cell = row.getCell(0);
            }

            System.out.println(getCellValue(cell));

        }


    }
}
